'use strict';

/** @type {import('sequelize-cli').Migration} */
module.exports = {
  async up(queryInterface, Sequelize) {
    // 1. 添加WhatsApp状态字段到用户表
    await queryInterface.addColumn('users', 'whatsappId', {
      type: Sequelize.STRING(100),
      allowNull: true,
      comment: '关联的WhatsApp账号ID'
    });

    await queryInterface.addColumn('users', 'whatsappStatus', {
      type: Sequelize.ENUM('disconnected', 'connecting', 'connected'),
      defaultValue: 'disconnected',
      allowNull: false
    });

    // 2. 创建用户活跃度表
    await queryInterface.createTable('user_activities', {
      id: {
        type: Sequelize.INTEGER,
        primaryKey: true,
        autoIncrement: true
      },
      userId: {
        type: Sequelize.INTEGER,
        allowNull: false,
        references: {
          model: 'users',
          key: 'id'
        }
      },
      date: {
        type: Sequelize.DATEONLY,
        allowNull: false
      },
      activeTime: {
        type: Sequelize.INTEGER,
        allowNull: false,
        defaultValue: 0,
        comment: '活跃时间，单位为秒'
      },
      messagesSent: {
        type: Sequelize.INTEGER,
        allowNull: false,
        defaultValue: 0,
        comment: '发送消息数量'
      },
      messagesReceived: {
        type: Sequelize.INTEGER,
        allowNull: false,
        defaultValue: 0,
        comment: '接收消息数量'
      },
      averageResponseTime: {
        type: Sequelize.INTEGER,
        allowNull: true,
        comment: '平均响应时间，单位为秒'
      },
      responseRate: {
        type: Sequelize.FLOAT,
        allowNull: true,
        comment: '响应率，0-1之间的小数'
      },
      contactCount: {
        type: Sequelize.INTEGER,
        allowNull: false,
        defaultValue: 0,
        comment: '互动的联系人数量'
      },
      sessionStarts: {
        type: Sequelize.INTEGER,
        allowNull: false,
        defaultValue: 0,
        comment: 'WhatsApp会话开始次数'
      },
      loginTime: {
        type: Sequelize.DATE,
        allowNull: true,
        comment: '首次登录时间'
      },
      logoutTime: {
        type: Sequelize.DATE,
        allowNull: true,
        comment: '最后登出时间'
      },
      createdAt: {
        type: Sequelize.DATE,
        allowNull: false
      },
      updatedAt: {
        type: Sequelize.DATE,
        allowNull: false
      }
    });

    // 创建用户活跃度表索引
    await queryInterface.addIndex('user_activities', ['userId']);
    await queryInterface.addIndex('user_activities', ['date']);
    await queryInterface.addIndex('user_activities', ['userId', 'date'], {
      unique: true
    });

    // 3. 创建用户会话表
    await queryInterface.createTable('user_sessions', {
      id: {
        type: Sequelize.INTEGER,
        primaryKey: true,
        autoIncrement: true
      },
      userId: {
        type: Sequelize.INTEGER,
        allowNull: false,
        references: {
          model: 'users',
          key: 'id'
        }
      },
      sessionToken: {
        type: Sequelize.STRING(100),
        allowNull: false
      },
      startTime: {
        type: Sequelize.DATE,
        allowNull: false
      },
      endTime: {
        type: Sequelize.DATE,
        allowNull: true
      },
      duration: {
        type: Sequelize.INTEGER,
        allowNull: true,
        comment: '会话持续时间，单位为秒'
      },
      activeTime: {
        type: Sequelize.INTEGER,
        allowNull: false,
        defaultValue: 0,
        comment: '会话期间活跃时间，单位为秒'
      },
      idleTime: {
        type: Sequelize.INTEGER,
        allowNull: false,
        defaultValue: 0,
        comment: '会话期间空闲时间，单位为秒'
      },
      ipAddress: {
        type: Sequelize.STRING(45),
        allowNull: true
      },
      userAgent: {
        type: Sequelize.STRING(255),
        allowNull: true
      },
      messagesSent: {
        type: Sequelize.INTEGER,
        allowNull: false,
        defaultValue: 0,
        comment: '本次会话发送消息数量'
      },
      messagesReceived: {
        type: Sequelize.INTEGER,
        allowNull: false,
        defaultValue: 0,
        comment: '本次会话接收消息数量'
      },
      contactsInteracted: {
        type: Sequelize.INTEGER,
        allowNull: false,
        defaultValue: 0,
        comment: '本次会话互动的联系人数量'
      },
      status: {
        type: Sequelize.ENUM('active', 'idle', 'closed'),
        defaultValue: 'active'
      },
      whatsappSessionId: {
        type: Sequelize.STRING(100),
        allowNull: true,
        comment: '关联的WhatsApp会话ID'
      },
      lastActivityTime: {
        type: Sequelize.DATE,
        allowNull: true,
        comment: '最后活跃时间'
      },
      closeReason: {
        type: Sequelize.STRING(50),
        allowNull: true,
        comment: '会话关闭原因'
      },
      createdAt: {
        type: Sequelize.DATE,
        allowNull: false
      },
      updatedAt: {
        type: Sequelize.DATE,
        allowNull: false
      }
    });

    // 创建用户会话表索引
    await queryInterface.addIndex('user_sessions', ['userId']);
    await queryInterface.addIndex('user_sessions', ['sessionToken']);
    await queryInterface.addIndex('user_sessions', ['startTime']);
    await queryInterface.addIndex('user_sessions', ['status']);

    // 4. 创建WhatsApp会话表
    await queryInterface.createTable('whatsapp_sessions', {
      id: {
        type: Sequelize.INTEGER,
        primaryKey: true,
        autoIncrement: true
      },
      userId: {
        type: Sequelize.INTEGER,
        allowNull: false,
        references: {
          model: 'users',
          key: 'id'
        }
      },
      sessionId: {
        type: Sequelize.STRING(100),
        allowNull: false,
        comment: 'WhatsApp会话唯一标识符'
      },
      status: {
        type: Sequelize.ENUM('disconnected', 'connecting', 'connected'),
        allowNull: false,
        defaultValue: 'disconnected'
      },
      startTime: {
        type: Sequelize.DATE,
        allowNull: false,
        comment: '会话开始时间'
      },
      endTime: {
        type: Sequelize.DATE,
        allowNull: true,
        comment: '会话结束时间'
      },
      duration: {
        type: Sequelize.INTEGER,
        allowNull: true,
        comment: '会话持续时间，单位为秒'
      },
      phoneNumber: {
        type: Sequelize.STRING(50),
        allowNull: true,
        comment: '关联的WhatsApp手机号码'
      },
      deviceInfo: {
        type: Sequelize.TEXT,
        allowNull: true,
        comment: 'WhatsApp设备信息'
      },
      loginDeviceType: {
        type: Sequelize.STRING(50),
        allowNull: true,
        comment: '登录设备类型'
      },
      batteryLevel: {
        type: Sequelize.INTEGER,
        allowNull: true,
        comment: '设备电池电量'
      },
      messageCount: {
        type: Sequelize.INTEGER,
        allowNull: false,
        defaultValue: 0,
        comment: '会话期间消息总数'
      },
      contactCount: {
        type: Sequelize.INTEGER,
        allowNull: false,
        defaultValue: 0,
        comment: '会话期间互动的联系人数量'
      },
      lastActivityTime: {
        type: Sequelize.DATE,
        allowNull: true,
        comment: '最后活动时间'
      },
      createdAt: {
        type: Sequelize.DATE,
        allowNull: false
      },
      updatedAt: {
        type: Sequelize.DATE,
        allowNull: false
      }
    });

    // 创建WhatsApp会话表索引
    await queryInterface.addIndex('whatsapp_sessions', ['userId']);
    await queryInterface.addIndex('whatsapp_sessions', ['sessionId']);
    await queryInterface.addIndex('whatsapp_sessions', ['status']);
    await queryInterface.addIndex('whatsapp_sessions', ['startTime']);
  },

  async down(queryInterface, Sequelize) {
    // 删除表和字段
    await queryInterface.removeColumn('users', 'whatsappId');
    await queryInterface.removeColumn('users', 'whatsappStatus');
    
    await queryInterface.dropTable('user_activities');
    await queryInterface.dropTable('user_sessions');
    await queryInterface.dropTable('whatsapp_sessions');
  }
}; 